package org.ekstrabilet.stadium.logic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.ekstrabilet.database.connection.ConnectionManager;
import org.ekstrabilet.database.statements.Statements;
import org.ekstrabilet.logic.exceptions.StadiumException;
import org.ekstrabilet.stadium.beans.Row;
import org.ekstrabilet.stadium.beans.Sector;


/**
 * Singleton, represents logic of a Sector, provides connection
 * between Sector object and database, implements methods responsible for
 * manipulating that object in database
 * @author Marcin
 *
 */
public class SectorLogic {
	
	private static SectorLogic sectorLogic= null;



	
	private SectorLogic(){
		
	}
	/**
	 * adds new entity to database based on Sector object
	 * @param sector
	 * @return id of added Sector
	 * @throws SQLException
	 */
	public int addSector(Sector sector) throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection(); 
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.INSERT_SECTOR);
			int id= getMaxId();
			if(id != -1){
				char[] tmp= new char[1];
				tmp[0]= sector.getSign();
				stm.setInt(1, id+1);
				stm.setInt(2, sector.getRows().size());
				stm.setInt(3, sector.getRow(0).getSeats().size());
				stm.setString(4, new String(tmp));
				stm.executeUpdate();
				return id+1;
			}
			
		} catch (SQLException e) {
			throw e;
			
		}
		return -1;
	}
	/**
	 * removes sector form database
	 * @param id sector id
	 * @throws SQLException
	 */
	public void removeSector(int id) throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection();
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.DELETE_SECTOR);
			stm.setInt(1, id);
			stm.executeUpdate();
		} catch (SQLException e) {
			throw e;
		}
	}
	/**
	 * gets entity from database and returns it as Sector object
	 * <br/> 
	 * <b>NOTE:</b> Sector rows are not initialized in this stage
	 * @param id id of sector
	 * @return Sector object 
	 */
	public Sector getSectorByID(int id){
		Sector sector= null;
		Connection conn= ConnectionManager.getManager().getConnection(); 
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.SELECT_SECTOR);
			stm.setInt(1, id);
			ResultSet rs= stm.executeQuery();

			while(rs.next()){
				sector= new Sector();
				int rows= rs.getInt("rowsNum");
				int seats= rs.getInt("seatsNum");
				String sign= rs.getString("token");
				sector.setSign(sign.charAt(0));
				sector.setPrice(getSectorPrice(id));
				sector.setCapacity(rows*seats);
				sector.setSectorId(id);
				sector.setRowsNum(rows);
				sector.setSeatsNum(seats);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sector;
	}
	/**
	 * gets rows of sector from database, used in lazy initialization
	 * @param sector 
	 * @return list of rows
	 */
	public ArrayList<Row> getRows(Sector sector){
		ArrayList<Row> rows= new ArrayList<Row>();
		for(int x=1; x<=sector.getRowsNum(); x++){
			Row row= new Row();
			row.setCapacity(sector.getSeatsNum());
			for(int y=1; y<=sector.getSeatsNum(); y++){
				SeatLogic logic= SeatLogic.getInstance();
				try {
					row.addSeat(logic.getSeat(x, y, sector.getSectorId()));
				} catch (SQLException e) {
					return null;
				}
			}
			rows.add(row);
		}
		return rows;
	}
	
	/**
	 * gets price of sector from database
	 * @param id sector id
	 * @return price of sector
	 * @throws SQLException
	 */
	public int getSectorPrice(int id) throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection(); 
		try {
			PreparedStatement stm= conn.prepareStatement(Statements.SELECT_PRICE);
			stm.setInt(1, id);
			ResultSet rs= stm.executeQuery();
			while(rs.next()){
				int price= rs.getInt("price");
				return price;
			}
		} catch (SQLException e) {
			throw e;
		}
		return -1;
	}
	/**
	 * 
	 * @return instance of SectorLogic
	 */
	public static SectorLogic getInstance(){
		if(sectorLogic == null)
			sectorLogic= new SectorLogic();
		return sectorLogic;
	}
	
	private int getMaxId() throws SQLException{
		Connection conn= ConnectionManager.getManager().getConnection(); 
		try {
			int maxId;
			Statement st= conn.createStatement();
			ResultSet rs= st.executeQuery(Statements.MAX_SECTOR_ID);
			
			while(rs.next()){
				maxId= rs.getInt("maxID");
				return maxId;
			}		
		} catch (SQLException e) {
			throw e;
		}
		
		return -1;
	}
}
